package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTMachineFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.SignalMachineDto;
import com.hualude.roadsign.dto.SignalMachineFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/7/22.
 */
public class SignalMachineRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<SignalMachineDto> getSignalMachine4DT(DTRequestDto dtRequestDto,
                                                      String actorIds,
                                                      Integer orgId,
                                                      Boolean audit,
                                                      String startRoad,
                                                      String endRoad,
                                                      Integer alarmBinding) throws Exception {
        String sql = "select sm.*, ur.UserName as signalMachineCreatorName, " +
                "og1.OrgName as signalMachineOrgName, og2.OrgName as signalMachineMainOrgName,a.DeviceState " +
                "from (tb_signal_machine sm, tb_user ur, tb_org og1, tb_org og2) " +
                "left join tb_alarm a on (sm.AlarmDeviceId=a.DeviceId) " +
                "where ur.UserId = sm.SignalMachineCreatorId " +
                "and og1.OrgId = sm.SignalMachineOrgId and og2.OrgId = sm.SignalMachineMainOrgId and " +
                " sm.SignalMachineStartRoad like '%" + startRoad + "%' and sm.SignalMachineEndRoad like '%" + endRoad + "%'" +
                " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and SignalMachineIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and sm.SignalMachineMainOrgId=" + orgId + " ";
        else
            sql += "and sm.SignalMachineOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select sm.*, ur.UserName as signalMachineCreatorName, " +
                    "og1.OrgName as signalMachineOrgName, og2.OrgName as signalMachineMainOrgName,a.DeviceState " +
                    "from (tb_signal_machine sm, tb_user ur, tb_org og1, tb_org og2) " +
                    "left join tb_alarm a on (sm.AlarmDeviceId=a.DeviceId) " +
                    "where ur.UserId = sm.SignalMachineCreatorId " +
                    "and og1.OrgId = sm.SignalMachineOrgId and og2.OrgId = sm.SignalMachineMainOrgId and " +
                    " sm.SignalMachineStartRoad like '%" + endRoad + "%' and sm.SignalMachineEndRoad like '%" + startRoad + "%'" +
                    " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql += "and SignalMachineIsComplete = 0 ";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and sm.SignalMachineMainOrgId=" + orgId + " ";
            else
                sql += "and sm.SignalMachineOrgId = " + orgId + " ";
        }
        sql += "order by SignalMachineInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();

    }

    public Integer getSignalMachine4DTCount(DTRequestDto dtRequestDto,
                                            String actorIds,
                                            Integer orgId,
                                            Boolean audit,
                                            String startRoad,
                                            String endRoad,
                                            Integer alarmBinding) throws Exception {
        String sql = "select count(*) " +
                "from tb_signal_machine sm " +
                " where sm.SignalMachineStartRoad like '%" + startRoad + "%' and sm.SignalMachineEndRoad like '%" + endRoad + "%'" +
                " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and SignalMachineIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and sm.SignalMachineMainOrgId=" + orgId + " ";
        else
            sql += "and sm.SignalMachineOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        Integer count1 = Integer.parseInt(query.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            sql = "select count(*) " +
                    "from tb_signal_machine sm " +
                    " where sm.SignalMachineStartRoad like '%" + endRoad + "%' and sm.SignalMachineEndRoad like '%" + startRoad + "%'" +
                    " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql += "and SignalMachineIsComplete = 0 ";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and sm.SignalMachineMainOrgId=" + orgId + " ";
            else
                sql += "and sm.SignalMachineOrgId = " + orgId + " ";
            query = em.createNativeQuery(sql);
            count2 = Integer.parseInt(query.getSingleResult().toString());
        }
        return count2 + count1;
    }


    public List<SignalMachineFaultDto> getMachineCoordinate4Map(Integer orgId, Integer actorId) throws Exception {
        String sql = "SELECT sm.SignalMachineId,sm.SignalMachineLongitude,sm.SignalMachineLatitude,sm.SignalMachinePhoto\n" +
                "FROM tb_signal_machine sm WHERE sm.SignalMachineIsComplete=1 ";
        if (actorId != 1) {
            sql += " and sm.SignalMachineOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineFaultDto.class));
        return query.getResultList();
    }

    public Integer getMachineFaultReportCount4DT(String search, Integer actorId, Integer orgId, String signalMachineStartRoad, String signalMachineEndRoad) throws Exception {
        String sql = "select count(*) " +
                "FROM ((tb_signal_machine sm JOIN tb_org o ON o.OrgId=sm.SignalMachineOrgId)\n" +
                "JOIN tb_user u ON u.UserId=sm.SignalMachineCreatorId)\n" +
                "WHERE sm.SignalMachineStartRoad LIKE '%" + signalMachineStartRoad + "%' AND sm.SignalMachineEndRoad LIKE '%" + signalMachineEndRoad + "%'";
        if (actorId != 1) {
            sql += " and sm.SignalMachineOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTMachineFaultDto> getMachineFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String signalMachineStartRoad, String signalMachineEndRoad) throws Exception {
        String sql = "SELECT sm.SignalMachineId,sm.SignalMachinePhoto,sm.SignalMachineProvince,sm.SignalMachineCity," +
                "sm.SignalMachineArea,sm.SignalMachineRegionId,o.OrgName AS machineOrgName,u.UserName AS signalMachineCreatorName," +
                "sm.SignalMachineInstallTime\n" +
                "FROM ((tb_signal_machine sm JOIN tb_org o ON o.OrgId=sm.SignalMachineOrgId)\n" +
                "JOIN tb_user u ON u.UserId=sm.SignalMachineCreatorId)\n" +
                "WHERE sm.SignalMachineStartRoad LIKE '%" + signalMachineStartRoad + "%' AND sm.SignalMachineEndRoad LIKE '%" + signalMachineEndRoad + "%'";
        if (actorId != 1) {
            sql += " and sm.SignalMachineOrgId=" + orgId + "";
        }
        if (signalMachineStartRoad != "" || signalMachineEndRoad != "") {
            sql += " UNION SELECT sm.SignalMachineId,sm.SignalMachinePhoto,sm.SignalMachineProvince,sm.SignalMachineCity," +
                    "sm.SignalMachineArea,sm.SignalMachineRegionId,o.OrgName AS machineOrgName,u.UserName AS signalMachineCreatorName," +
                    "sm.SignalMachineInstallTime\n" +
                    "FROM ((tb_signal_machine sm JOIN tb_org o ON o.OrgId=sm.SignalMachineOrgId)\n" +
                    "JOIN tb_user u ON u.UserId=sm.SignalMachineCreatorId)\n" +
                    "WHERE sm.SignalMachineStartRoad LIKE '%" + signalMachineEndRoad + "%' AND sm.SignalMachineEndRoad LIKE '%" + signalMachineStartRoad + "%'";
        }
        if (actorId != 1) {
            sql += " and sm.SignalMachineOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMachineFaultDto.class));
        List<DTMachineFaultDto> machineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return machineFaultDtos;
    }

    public List<String> getStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT sm.SignalMachineStartRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " WHERE sm.SignalMachineMainOrgId=" + orgId + " ";
            else
                sql += " WHERE sm.SignalMachineOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT sm.SignalMachineEndRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " WHERE sm.SignalMachineMainOrgId=" + orgId + " ";
            else
                sql += " WHERE sm.SignalMachineOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionByStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT sm.SignalMachineStartRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm\n" +
                "WHERE sm.SignalMachineEndRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalMachineMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalMachineOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT sm.SignalMachineEndRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm\n" +
                "WHERE sm.SignalMachineStartRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalMachineMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalMachineOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getStartRoadByCity(String city, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.SignalMachineStartRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm where sm.signalMachineCity like '%" + city + "%'";

        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.SignalMachineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.SignalMachineOrgId=" + orgId + " ";
        sql += " UNION SELECT sm.SignalMachineEndRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm where sm.signalMachineCity like '%" + city + "%'";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.SignalMachineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.SignalMachineOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<SignalMachineDto> getSignalMachineCoordinate4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "select signalMachineId, signalMachineLongitude as longitude ,signalMachineLatitude as latitude from tb_signal_machine ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "where SignalMachineOrgId=" + orgId + "";
            if (orgType == 1)
                sql += "where SignalMachineMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        return query.getResultList();
    }

    public List<SignalMachineDto> getSignalMachine4APP(String actorIds,
                                                       Integer orgId,
                                                       Integer mCount,
                                                       Integer mSize,
                                                       String city,
                                                       String startRoad,
                                                       String endRoad,
                                                       String alarmType) throws Exception {
        String sql = "select se.*, ur.UserName as signalMachineCreatorName, " +
                "og1.OrgName as signalMachineOrgName, og2.OrgName as signalMachineMainOrgName,a.DeviceState " +
                "from (tb_signal_machine se, tb_user ur, tb_org og1, tb_org og2) " +
                "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                "where ur.UserId = se.signalMachineCreatorId " +
                "and og1.OrgId = se.signalMachineOrgId and og2.OrgId = se.signalMachineMainOrgId " +
                "and se.signalMachineStartRoad like '%" + startRoad + "%' and se.signalMachineEndRoad like '%" + endRoad + "%' " +
                "and se.signalMachineCity like '%" + city + "%'";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and se.signalMachineMainOrgId=" + orgId + " ";
        else
            sql += "and se.signalMachineOrgId = " + orgId + " ";
        if (alarmType.contains("0"))
            sql += " and se.AlarmDeviceId is NULL ";
        else if (alarmType.contains("1"))
            sql += " and se.AlarmDeviceId is not NULL ";
        else
            sql += "";
        if (startRoad != "" || endRoad != "") {
            sql += "union select se.*, ur.UserName as signalMachineCreatorName, " +
                    "og1.OrgName as signalMachineOrgName, og2.OrgName as signalMachineMainOrgName,a.DeviceState " +
                    "from (tb_signal_machine se, tb_user ur, tb_org og1, tb_org og2) " +
                    "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                    "where ur.UserId = se.signalMachineCreatorId " +
                    "and og1.OrgId = se.signalMachineOrgId and og2.OrgId = se.signalMachineMainOrgId " +
                    "and se.signalMachineStartRoad like '%" + endRoad + "%' and se.signalMachineEndRoad like '%" + startRoad + "%' " +
                    "and  se.signalMachineCity like '%" + city + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and se.signalMachineMainOrgId=" + orgId + " ";
            else
                sql += "and se.signalMachineOrgId = " + orgId + " ";
            if (alarmType.contains("0"))
                sql += " and se.AlarmDeviceId is NULL ";
            else if (alarmType.contains("1"))
                sql += " and se.AlarmDeviceId is not NULL ";
            else
                sql += "";
        }
        sql += "order by concat(signalMachineInstallTime, signalMachineOrgId) desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<SignalMachineDto> getSignalMachineById(Integer signalMachineId) throws Exception {
        String sql = "select se.* ,o1.orgName as signalMachineOrgName,o2.OrgName as signalMachineMainOrgName," +
                " u.userName as signalMachineCreatorName from tb_signal_machine se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.signalMachineOrgId and o2.orgId=se.signalMachineMainOrgId and u.UserId=se.signalMachineCreatorId" +
                " and se.signalMachineId=" + signalMachineId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        return query.getResultList();
    }

    public List<String> getCity4SignalMachine(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT SignalMachineCity from tb_signal_machine ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " where SignalMachineMainOrgId=" + orgId + " ";
        else
            sql += " where SignalMachineOrgId=" + orgId + "";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<SignalMachineDto> getSignalMachine4Map4App(Integer orgId,
                                                           String actorIds,
                                                           double neLongitude,
                                                           double neLatitude,
                                                           double swLongitude,
                                                           double swLatitude) throws Exception {
        String sql = "select * from tb_signal_machine ";
        sql += "where signalMachineIsComplete = 1 " +
                "and signalMachineLongitude < :neLongitude and signalMachineLongitude > :swLongitude " +
                "and signalMachineLatitude < :neLatitude and signalMachineLatitude > :swLatitude ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and SignalMachineMainOrgId = " + orgId + " ";
        else
            sql += "and SignalMachineOrgId = " + orgId + "";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        return query.getResultList();
    }

    public List<String> getSectionByStartRoad4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.SignalMachineStartRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm\n" +
                "WHERE sm.SignalMachineEndRoad LIKE '%" + roadName + "%' and sm.SignalMachineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.SignalMachineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.SignalMachineOrgId=" + orgId + " ";

        sql += " UNION SELECT sm.SignalMachineEndRoad AS signalMachineStartRoad\n" +
                "FROM tb_signal_machine sm\n" +
                "WHERE sm.SignalMachineStartRoad LIKE '%" + roadName + "%' and sm.SignalMachineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.SignalMachineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.SignalMachineOrgId=" + orgId + " ";

        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<SignalMachineDto> getDeviceInfo(Integer deviceId) throws Exception {
        String sql = "select sm.signalMachineId,sm.SignalMachineProvince,sm.SignalMachineCity,sm.SignalMachineArea,sm.SignalMachineStartRoad,sm.SignalMachineEndRoad," +
                "sm.SignalMachinePhoto,sm.SignalMachineOrgId,sm.SignalMachineMainOrgId,sm.alarmDeviceId,o1.OrgName as signalMachineOrgName,o2.OrgName as signalMachineMainOrgName," +
                "sm.ProjectNumber,sm.SignalMachineRegionId,sm.SignalMachineLongitude,sm.SignalMachineLatitude,sm.SignalMachineType,sm.SignalMachineBrand," +
                "sm.SignalMachineNumber,sm.SignalMachineIsAlarm,sm.SignalMachineInstallOrg,sm.SignalMachineInstallTime,sm.SignalMachinePeriod,sm.SignalMachineServiceLife," +
                "sm.SignalMachineRemark,sm.SignalMachineCreatorId,sm.SignalMachineModifierId,sm.SignalMachineIsComplete,sm.SignalMachineADeviceId,sm.AlarmCount " +
                "from tb_signal_machine sm,tb_org o1,tb_org o2 where sm.SignalMachineOrgId = o1.OrgId and sm.SignalMachineMainOrgId = o2.OrgId and sm.SignalMachineId = " + deviceId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        return query.getResultList();
    }
}
